﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class DoiBong_DAO
    {
        public static DataTable SelectAll()
        {
            string sql = "select * from DOIBONG";
            return DataAccess.executeQuery(sql);
        }
        public static DataTable SelectTen()
        {
            string sql = "select MaDoiBong,TenDoiBong from DOIBONG";
            return DataAccess.executeQuery(sql);
        }

        public static DoiBong_DTO SelectByID(int id)
        {
            string sql = "select * from DOIBONG where MaDoiBong = " + id;
            DataTable dt = DataAccess.executeQuery(sql);
            DoiBong_DTO dto = new DoiBong_DTO();
            dto.MaDoiBong = Convert.ToInt32(dt.Rows[0]["MaDoiBong"]);
            dto.TenDoiBong = Convert.ToString(dt.Rows[0]["TenDoiBong"]);
            dto.HLV = Convert.ToString(dt.Rows[0]["HLV"]);
            dto.MaSan = Convert.ToInt32(dt.Rows[0]["MaSan"]);
            dto.Thang = Convert.ToInt32(dt.Rows[0]["Thang"]);
            dto.Hoa = Convert.ToInt32(dt.Rows[0]["Hoa"]);
            dto.Thua = Convert.ToInt32(dt.Rows[0]["Thua"]);
            dto.BanThang = Convert.ToInt32(dt.Rows[0]["BanThang"]);
            dto.BanThua = Convert.ToInt32(dt.Rows[0]["BanThua"]);
            dto.Diem = Convert.ToInt32(dt.Rows[0]["Diem"]);
            dto.ByteLogo = (byte[])dt.Rows[0]["Logo"];

            return dto;
        }
        public static DataTable SelectByTen(string ten)
        {
            string sql = "select * from DOIBONG where TenDoiBong = '" + ten + "'";
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static DataTable SelectBySan(int san)
        {
            string sql = "select * from DOIBONG where MaSan = " + san + "";
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static void Insert(DoiBong_DTO dto)
        {
            string sql = "Insert INTO DOIBONG (TenDoiBong, HLV, MaSan, Thang, Hoa, Thua, BanThang, BanThua, Diem, Logo) VALUES ('" +
                                dto.TenDoiBong + 
                                "','" + dto.HLV + 
                                "'," + dto.MaSan + 
                                "," + dto.Thang + 
                                "," + dto.Hoa + 
                                "," + dto.Thua + 
                                "," + dto.BanThang + 
                                "," + dto.BanThua + 
                                "," + dto.Diem +
                                ", @MyLogo)";

            OleDbCommand command = new OleDbCommand(sql);
            OleDbParameter imageParameter = command.Parameters.AddWithValue("@MyLogo", SqlDbType.Binary);
            imageParameter.Value = dto.ByteLogo;
            imageParameter.Size = dto.ByteLogo.Length;
            DataAccess.executeNonQuery(command);
        }

        public static void Update(DoiBong_DTO dto)
        {
            string sql = "Update DOIBONG set TenDoiBong=('" + dto.TenDoiBong +
                                            "'),HLV=('" + dto.HLV +
                                            "'),MaSan=(" + dto.MaSan +
                                            "),Thang=(" + dto.Thang +
                                            "),Hoa=(" + dto.Hoa +
                                            "),Thua=(" + dto.Thua +
                                            "),BanThang=(" + dto.BanThang +
                                            "),BanThua=(" + dto.BanThua +
                                            "),Diem=(" + dto.Diem +
                                            "), Logo = (@MyLogo) where MaDoiBong = " + dto.MaDoiBong + "";
            OleDbCommand command = new OleDbCommand(sql);
            OleDbParameter imageParameter = command.Parameters.AddWithValue("@MyLogo", SqlDbType.Binary);
            imageParameter.Value = dto.ByteLogo;
            imageParameter.Size = dto.ByteLogo.Length;
            DataAccess.executeNonQuery(command);
        }
        public static void Delete(int ma)
        {
            string sql = "Delete from DOIBONG where MaDoiBong = " + ma + "";
            DataAccess.executeNonQuery(sql);
        }
    }
}
